using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Integrator.DAL
{
    public class LocalProduct
    {
        public string connString = "Server=localhost;Database=Integrator;User ID=sa;Password=sa;Connection Reset=FALSE;Min Pool Size=2;Connection Lifetime=120;Connection Timeout=60;";

        public string MaChiNhanh = string.Empty;
        public string PhongGiaoDich = string.Empty;
        public string CapUyThac = string.Empty;
        public string MaChuongTrinh = string.Empty;
        public string MaDuAn = string.Empty;
        public string TenDuAn = string.Empty;
        public string NgayHieuLuc = string.Empty;
        public string NgayHetHan = string.Empty;
        public string NhomKhachHang = string.Empty;
        public string GioiTinh = string.Empty;
        public string PhamViDanToc = string.Empty;
        public string TaiSanDamBao = string.Empty;
        public string ApDungDoanhNghiep = string.Empty;
        public string LaiSuatTrongHan = string.Empty;
        public string LaiSuatQuaHan = string.Empty;
        public string HinhThucGiaiNgan = string.Empty;
        public string ChoVayLuuVu = string.Empty;
        public string GiaHan = string.Empty;
        public string VungChoVay = string.Empty;
        public string DinhKyTraGoc = string.Empty;
        public string ChuyenNoQuaHan = string.Empty;
        public string LoaiKyHan = string.Empty;
        public string DinhKyTraLai = string.Empty;
        public string ChoVayUyThac = string.Empty;
        public string ChoVayToDA = string.Empty;
        public string TinhHoaHong = string.Empty;
        public string PhiUyThac = string.Empty;
        public string PhanBoLai = string.Empty;
        public string DinhKyPhanBoLai = string.Empty;
        public string NoiDungPhanBo = string.Empty;
        public string DoiTuongKhachHang = string.Empty;
        public string GiamLai = string.Empty;
        public string HoTroLaiSuat = string.Empty;
        public string LSDuocHoTro = string.Empty;
        public string HinhThucHTLS = string.Empty;
        public string HoTroToanBo = string.Empty;
        public string MucVayThapNhat = string.Empty;
        public string MucVayCaoNhat = string.Empty;
        public string MucVayMacDinh = string.Empty;
        public string ThoiGianToiThieu = string.Empty;
        public string ThoiGianToiDa = string.Empty;
        public string ThoiGianMacDinh = string.Empty;
        public string ThoiGianAnHan = string.Empty;
        public string TinhLaiAnHan = string.Empty;
        public string PhanBoLaiAnHan = string.Empty;
        public string DuNoTinhLai = string.Empty;
        public string XacDinhNgayTinhLai = string.Empty;
        public string LaiSuatLuuVu = string.Empty;
        public string LaiSuatGiaHan = string.Empty;
        public string HinhThucXLRuiRo = string.Empty;


        public void Delete(string branchCode)
        {
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("DELETE [dbo].[tblSanPhamDiaPhuong] WHERE [MaChiNhanh] = @MaChiNhanh", conn);
                cmd.Parameters.AddWithValue("@MaChiNhanh", branchCode);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public void Save()
        {            
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[tblSanPhamDiaPhuong](MaChiNhanh, PhongGiaoDich, CapUyThac, MaChuongTrinh, MaDuAn, TenDuAn, NgayHieuLuc, NgayHetHan, NhomKhachHang, GioiTinh, PhamViDanToc, TaiSanDamBao, ApDungDoanhNghiep, LSTrongHan, LSQuaHan, HinhThucGiaiNgan, ChoVayLuuVu, GiaHan, VungChoVay, DinhKyTraGoc, ChuyenNoQuaHan, LoaiKyHan, DinhKyTraLai, ChoVayUyThac, ChoVayToDA, TinhHoaHong, PhiUyThac, PhanBoLai, DinhKyPhanBoLai, NoiDungPhanBo, DoiTuongKhachHang, GiamLai, HoTroLaiSuat, LSDuocHoTro, HinhThucHTLS, HoTroToanBo, MucVayThapNhat, MucVayCaoNhat, MucVayMacDinh, ThoiGianToiThieu, ThoiGianToiDa, ThoiGianMacDinh, ThoiGianAnHan, TinhLaiAnHan, PhanBoLaiAnHan, DuNoTinhLai, XacDinhNgayTinhLai, LaiSuatLuuVu, LaiSuatGiaHan, HinhThucXLRuiRo)VALUES(@MaChiNhanh, @PhongGiaoDich, @CapUyThac, @MaChuongTrinh, @MaDuAn, @TenDuAn, @NgayHieuLuc, @NgayHetHan, @NhomKhachHang, @GioiTinh, @PhamViDanToc, @TaiSanDamBao, @ApDungDoanhNghiep, @LSTrongHan, @LSQuaHan, @HinhThucGiaiNgan, @ChoVayLuuVu, @GiaHan, @VungChoVay, @DinhKyTraGoc, @ChuyenNoQuaHan, @LoaiKyHan, @DinhKyTraLai, @ChoVayUyThac, @ChoVayToDA, @TinhHoaHong, @PhiUyThac, @PhanBoLai, @DinhKyPhanBoLai, @NoiDungPhanBo, @DoiTuongKhachHang, @GiamLai, @HoTroLaiSuat, @LSDuocHoTro, @HinhThucHTLS, @HoTroToanBo, @MucVayThapNhat, @MucVayCaoNhat, @MucVayMacDinh, @ThoiGianToiThieu, @ThoiGianToiDa, @ThoiGianMacDinh, @ThoiGianAnHan, @TinhLaiAnHan, @PhanBoLaiAnHan, @DuNoTinhLai, @XacDinhNgayTinhLai, @LaiSuatLuuVu, @LaiSuatGiaHan, @HinhThucXLRuiRo)", conn);
                cmd.Parameters.AddWithValue("@MaChiNhanh", MaChiNhanh);
                cmd.Parameters.AddWithValue("@PhongGiaoDich", PhongGiaoDich);
                cmd.Parameters.AddWithValue("@CapUyThac", CapUyThac);
                cmd.Parameters.AddWithValue("@MaChuongTrinh", MaChuongTrinh);
                cmd.Parameters.AddWithValue("@MaDuAn", MaDuAn);
                cmd.Parameters.AddWithValue("@TenDuAn", TenDuAn);
                cmd.Parameters.AddWithValue("@NgayHieuLuc", NgayHieuLuc);
                cmd.Parameters.AddWithValue("@NgayHetHan", NgayHetHan);
                cmd.Parameters.AddWithValue("@NhomKhachHang", NhomKhachHang);
                cmd.Parameters.AddWithValue("@GioiTinh", GioiTinh);
                cmd.Parameters.AddWithValue("@PhamViDanToc", PhamViDanToc);
                cmd.Parameters.AddWithValue("@TaiSanDamBao", TaiSanDamBao);
                cmd.Parameters.AddWithValue("@ApDungDoanhNghiep", ApDungDoanhNghiep);
                cmd.Parameters.AddWithValue("@LSTrongHan", LaiSuatTrongHan);
                cmd.Parameters.AddWithValue("@LSQuaHan", LaiSuatQuaHan);
                cmd.Parameters.AddWithValue("@HinhThucGiaiNgan", HinhThucGiaiNgan);
                cmd.Parameters.AddWithValue("@ChoVayLuuVu", ChoVayLuuVu);
                cmd.Parameters.AddWithValue("@GiaHan", GiaHan);
                cmd.Parameters.AddWithValue("@VungChoVay", VungChoVay);
                cmd.Parameters.AddWithValue("@DinhKyTraGoc", DinhKyTraGoc);
                cmd.Parameters.AddWithValue("@ChuyenNoQuaHan", ChuyenNoQuaHan);
                cmd.Parameters.AddWithValue("@LoaiKyHan", LoaiKyHan);
                cmd.Parameters.AddWithValue("@DinhKyTraLai", DinhKyTraLai);
                cmd.Parameters.AddWithValue("@ChoVayUyThac", ChoVayUyThac);
                cmd.Parameters.AddWithValue("@ChoVayToDA", ChoVayToDA);
                cmd.Parameters.AddWithValue("@TinhHoaHong", TinhHoaHong);
                cmd.Parameters.AddWithValue("@PhiUyThac", PhiUyThac);
                cmd.Parameters.AddWithValue("@PhanBoLai", PhanBoLai);
                cmd.Parameters.AddWithValue("@DinhKyPhanBoLai", DinhKyPhanBoLai);
                cmd.Parameters.AddWithValue("@NoiDungPhanBo", NoiDungPhanBo);                
                cmd.Parameters.AddWithValue("@DoiTuongKhachHang", DoiTuongKhachHang);
                cmd.Parameters.AddWithValue("@GiamLai", GiamLai);
                cmd.Parameters.AddWithValue("@HoTroLaiSuat", HoTroLaiSuat);
                cmd.Parameters.AddWithValue("@LSDuocHoTro", LSDuocHoTro);
                cmd.Parameters.AddWithValue("@HinhThucHTLS", HinhThucHTLS);
                cmd.Parameters.AddWithValue("@HoTroToanBo", HoTroToanBo);
                cmd.Parameters.AddWithValue("@MucVayThapNhat", MucVayThapNhat);
                cmd.Parameters.AddWithValue("@MucVayCaoNhat", MucVayCaoNhat);
                cmd.Parameters.AddWithValue("@MucVayMacDinh", MucVayMacDinh);
                cmd.Parameters.AddWithValue("@ThoiGianToiThieu", ThoiGianToiThieu);
                cmd.Parameters.AddWithValue("@ThoiGianToiDa", ThoiGianToiDa);
                cmd.Parameters.AddWithValue("@ThoiGianMacDinh", ThoiGianMacDinh);
                cmd.Parameters.AddWithValue("@ThoiGianAnHan", ThoiGianAnHan);
                cmd.Parameters.AddWithValue("@TinhLaiAnHan", TinhLaiAnHan);
                cmd.Parameters.AddWithValue("@PhanBoLaiAnHan", PhanBoLaiAnHan);
                cmd.Parameters.AddWithValue("@DuNoTinhLai", DuNoTinhLai);
                cmd.Parameters.AddWithValue("@XacDinhNgayTinhLai", XacDinhNgayTinhLai);
                cmd.Parameters.AddWithValue("@LaiSuatLuuVu", LaiSuatLuuVu);
                cmd.Parameters.AddWithValue("@LaiSuatGiaHan", LaiSuatGiaHan);
                cmd.Parameters.AddWithValue("@HinhThucXLRuiRo", HinhThucXLRuiRo);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }


        public DataTable GetAll()
        {
            DataTable result = new DataTable();
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT rank() OVER (ORDER BY [ID]) AS STT, [MaChiNhanh], [PhongGiaoDich], [CapUyThac], [MaChuongTrinh], [MaDuAn], [TenDuAn], [NgayHieuLuc], [NgayHetHan], [NhomKhachHang], [GioiTinh], [PhamViDanToc], [TaiSanDamBao], [ApDungDoanhNghiep], [LSTrongHan], [LSQuaHan], [HinhThucGiaiNgan], [ChoVayLuuVu], [GiaHan], [VungChoVay], [DinhKyTraGoc], [ChuyenNoQuaHan], [LoaiKyHan], [DinhKyTraLai], [ChoVayUyThac], [ChoVayToDA], [TinhHoaHong], [PhiUyThac], [PhanBoLai], [DinhKyPhanBoLai], [NoiDungPhanBo], [DoiTuongKhachHang], [GiamLai], [HoTroLaiSuat], [LSDuocHoTro], [HinhThucHTLS], [HoTroToanBo], [MucVayThapNhat], [MucVayCaoNhat], [MucVayMacDinh], [ThoiGianToiThieu], [ThoiGianToiDa], [ThoiGianMacDinh], [ThoiGianAnHan], [TinhLaiAnHan], [PhanBoLaiAnHan], [DuNoTinhLai], [XacDinhNgayTinhLai], [LaiSuatLuuVu], [LaiSuatGiaHan], [HinhThucXLRuiRo] FROM [dbo].[tblSanPhamDiaPhuong] ORDER BY [MaChiNhanh]", conn);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return null;
        }

        public DataTable GetBranchs()
        {
            DataTable result = new DataTable();
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT BranchCode, ('['+BranchCode+'] '+ BranchName) AS BranchName FROM [dbo].[tblBranchs] ORDER BY [BranchName]", conn);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return null;
        }

        public DataTable GetAllBranchs()
        {
            DataTable result = new DataTable();
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT BranchCode, BranchName FROM [dbo].[tblBranchs] ORDER BY [BranchCode]", conn);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return null;
        }

    }
}